import pymysql
import pandas as pd
from datetime import datetime
from db_conn.dbConn import conn_db,conn_db_engine
import pandas as pd
import time
from sqlalchemy import create_engine
from db_conn.dbConn import conn_db_engine
conn1 = conn_db_engine('iot_local')
conn2 = conn_db_engine('ryerp_local')
connect = conn_db('ryerp_local')

# 上一次更新时间
from datetime import datetime


def update_device_vision_day(start_time):
    # 定义早上8点和晚上8点的时间
    morning_time = start_time.replace(hour=8, minute=0, second=0, microsecond=0)
    evening_time = start_time.replace(hour=20, minute=0, second=0, microsecond=0)
    yesterday_evening_time = evening_time - pd.DateOffset(days=1)
    # 检查当前时间是在早上8点和晚上8点之间，还是在晚上8点之后
    if start_time >= evening_time:
    # 如果当前时间在晚上8点之后，那么前一个时间点是晚上8点
        previous_time_point = evening_time
    elif start_time >= morning_time:
    # 如果当前时间在早上8点和晚上8点之间，那么前一个时间点是早上8点
        previous_time_point = morning_time
        day_night = '日班'
        production_date = start_time.strftime('%Y-%m-%d')
    elif start_time < morning_time:
    # 如果当前时间在早上8点之前，那么前一个时间点是昨天晚上8点
        previous_time_point = yesterday_evening_time
        day_night = '夜班'
        production_date = (start_time - pd.DateOffset(days=1)).strftime('%Y-%m-%d')

    #持续时间
    duration = start_time - previous_time_point
    duration_s = duration.total_seconds()
    print(duration_s)
    # 编写SQL查询语句
    #previous_time_point_str = previous_time_point.strftime('%Y-%m-%d %H:%M:%S')
    sql1 = 'SELECT * FROM iot_cache WHERE receive_time >= %s and receive_time < %s'
    # 使用Pandas的read_sql函数执行SQL语句并获取数据
    df_iot = pd.read_sql(sql1,con=conn1,params=(previous_time_point,start_time))
    #去掉df_iot中grp为6，dev为144的数据
    df_iot = df_iot[~((df_iot['grp'] == 6) & (df_iot['dev'] == 144))]
    #去掉df_iot中dev为169,170,171且num>20000的数据
    df_iot = df_iot[~(((df_iot['dev'] == 169) | (df_iot['dev'] == 170) | (df_iot['dev'] == 171)) & (df_iot['num'] > 20000))]



    #获取最近一个信号
    df_iot_last_signal = df_iot.groupby('dev').last().reset_index()
    df_iot_last_signal['dev_status'] = df_iot_last_signal['type'].apply(lambda x: 1 if x == '2' else 0)
    #df_iot_last_signal 对于每个dev若receive_time与start_time差距大于20分钟则dev_status为4
    df_iot_last_signal.loc[(start_time - df_iot_last_signal['receive_time']).dt.total_seconds() > 120, 'dev_status'] = 0
    df_iot_last_signal.loc[(start_time - df_iot_last_signal['receive_time']).dt.total_seconds() > 2400, 'dev_status'] = 4
    df_iot_status = df_iot_last_signal[['dev','dev_status']]
   # print(df_iot_status)
    df = df_iot_status



#---------获取最后一次预警持续时间----------#
    df_iot_fix = pd.merge(df, df_iot, on='dev', how='left')

    df_iot_WARN = df_iot_fix[df_iot_fix['dev_status'] == 1][['dev','type','receive_time']]
    #将duration改成last_warn_time
    dev_list = df_iot_WARN['dev'].drop_duplicates().tolist()
    dev_groups = df_iot_WARN.groupby('dev')
    dev_arrays = {dev: group['type'].tolist() for dev, group in dev_groups}

    def count_consecutive_warns(lst):
        count = 0
        for item in reversed(lst):
            if item == '2':
                count += 1
            else:
                break
        return count

    last_warn_time = []
    for dev in dev_list:
        consecutive_warns = count_consecutive_warns(dev_arrays[dev])
        last_warn_time.append([dev, consecutive_warns * 0.084])
      #  print('consecutive_warns',consecutive_warns)
    df_last_warn_time = pd.DataFrame(last_warn_time, columns=['dev', 'last_warn_time'])

    # print(df_last_warn_time)
    df = pd.merge(df, df_last_warn_time, on='dev', how='left')
    # 如果最后一次预警持续时间超过30分钟则算为离线，即对于df last_warn_time大于30则dev_status=4
    df.loc[df['last_warn_time'] > 30, 'dev_status'] = 5




# --------------获取预警次数------------#
    df_warn_times = df_iot.sort_values(by=['dev', 'receive_time'])

    # 使用shift()获取前一条记录的type
    df_warn_times['prev_type'] = df_warn_times.groupby('dev')['type'].shift(1)
    # 筛选出type为'2'且前一条记录的type不为'2'的行
    filtered_df = df_warn_times[(df_warn_times['type'] == '2') & (df_warn_times['prev_type'] == '1')]
    # 按照dev分组，并计算符合条件的记录数量
    df_warn_times = filtered_df.groupby('dev').size().reset_index(name='warn_num')[['dev','warn_num']]
    #print(df_warn_times)
    df = pd.merge(df, df_warn_times, on='dev', how='left')




#-----------获取每个dev的总预警数据------------#
    df_iot_warn_num = df_iot[df_iot['type']=='2'][['dev','receive_time']]
    df_iot_warn_num['hour_minute'] = df_iot_warn_num['receive_time'].dt.strftime('%H:%M')
    df_iot_time = df_iot_warn_num.groupby(['dev','hour_minute']).first().reset_index()
    df_iot_time = df_iot_time[['dev','hour_minute']].groupby('dev').size().reset_index(name='warn_time')
    df_iot_time['warn_time'] = df_iot_time['warn_time']*1.00


    if df_iot_warn_num.empty:
        print('没有预警数据')
    else:
        df = pd.merge(df, df_iot_time, on='dev', how='left')
        df['work_time'] = df['warn_time'].apply(
            lambda x: (duration_s - 60.0 * x) / 3600.0 if x > 0.0 else duration_s / 3600.0
        )
        #print(df)
    df['work_time'] = df['work_time'].fillna(duration_s / 3600)
    #print(df[df['dev']==52][['dev', 'work_time', 'warn_time']])
    df['work_percent'] = df['work_time'] * 3600 / duration_s

#-----------获取袜子数量信号----------------#
    df_iot_first_sock = df_iot[df_iot['type']=='1'].groupby('dev').first().reset_index()[['dev','num']]
    df_iot_last_sock = df_iot[df_iot['type']=='1'].groupby('dev').last().reset_index()[['dev','num']]
    df_iot_first_sock = df_iot_first_sock.rename(columns={'num':'num_first'})
    df_iot_last_sock = df_iot_last_sock.rename(columns={'num':'num_last'})
    #去掉num_first和num_last两列
    df_iot_sock = pd.merge(df_iot_last_sock,df_iot_first_sock,on='dev')
    df_iot_sock['production'] = df_iot_sock['num_last'] - df_iot_sock['num_first']
    df_iot_sock = df_iot_sock.drop(columns=['num_first', 'num_last'])


    if df_iot_sock.empty:
        print('没有生产数据')
    else:
        df = pd.merge(df,df_iot_sock,on='dev',how='left')


#--------------平均时长--------------------------#

    df_sock_time = df_iot[df_iot['type'] > '0']

    def filter_consecutive_type_1(group):
        # 创建一个新列，表示上一行的type
        group['prev_type'] = group['type'].shift(1)
        group['prev_num'] = group['num'].shift(1)
        group['prev_receive_time'] = group['receive_time'].shift(1)
        # 筛选出连续两行type都为1的行
        return group[(group['type'] == '1') & (group['prev_type'] == '1')]

    # 应用上述函数
    df_sock_time = df_sock_time.groupby('dev').apply(filter_consecutive_type_1).drop(columns=['prev_type'])
    df_sock_time = df_sock_time.reset_index(drop=True)
    df_sock_time['avg_time'] = df_sock_time.apply(
        lambda row: (row['receive_time'] - row['prev_receive_time']).total_seconds() / (row['num'] - row['prev_num']) if
        row['num'] != row['prev_num'] else 0, axis=1)
    df_sock_time['avg_time'] = df_sock_time['avg_time'].apply(lambda x: x if x < 210 and x > 15 else -1.0)
    df_sock_time = df_sock_time[df_sock_time['avg_time'] != -1.0]
    df_sock_time = df_sock_time[['dev', 'avg_time']].groupby('dev').mean().reset_index()
    df = pd.merge(df, df_sock_time[['dev', 'avg_time']].groupby('dev').mean().reset_index(), on='dev', how='left')




    #整合袜机排产
    sql2 = 'SELECT device_id,goods_id,group_id,goods_style,batch_id,status,operate_time,goods_id_old,goods_style_old,stuff_group FROM mm_new_device'
    df_ry = pd.read_sql(sql2, con=conn2)
    #df的dev改为device_id
    df = df.rename(columns={'dev': 'device_id'})
    #df右join df_ry
    df = pd.merge(df, df_ry, on='device_id', how='right')

    #若df的status为2、3、5，则保持原值，若为0，1，4则替换为dev_status的值
    df['status'] = df.apply(lambda row: row['status'] if row['status'] in [2, 3, 5] else row['dev_status'], axis=1)
    df['status'] = df['status'].fillna(4)
    #去掉dev_status
    df = df.drop(columns=['dev_status'])

    #--------增加日期和班次-------#
    df['date'] = production_date
    df['day_night'] = day_night


    # # -----------整合iot状态表---------------#
    # df_iot_state = pd.read_sql('select * from iot_state', con=conn1)
    # # 将df_iot_state的dev_id列名改成dev
    # df = pd.merge(df, df_iot_state, on='dev', how='left')
    # # 若df的status为3，则保持原值，若为0，1，4则替换为state的值
    # df['status'] = df.apply(lambda row: row['status'] if row['status'] == 3 else row['state'], axis=1)
    # # 去掉state
    # df = df.drop(columns=['state'])


# -----------将机器数据写入数据库mm_new_device_vision_day表----------------#
    #去掉work_percent小于50%的数据
    df = df[df['work_percent'] >= 0.7]
    #保留production在100-1000之间的数据
    df = df[(df['production'] >= 10) & (df['production'] <= 1000)]
    print(df)
    df.to_sql('mm_new_device_vision_day_test', con=conn2, if_exists='append', index=False)
    print('写入数据库成功')

# #-------------将每日织造数据统计放入数据库mm_new_device_vision_day_stat表中----------#
#     #保留warn_num,warn_time,work_percent,production,date,day_night这些列
#     df_stat = df[['warn_num','warn_time','work_percent','production','date','day_night']]
#     df_stat['dev_num'] = 1
#     #计算每日织造数据统计，对'warn_num','warn_time','work_percent','production'求平均,'production','dev_num'求和,注意要对'date','day_night'分组。
#     df_stat = df_stat.groupby(['date','day_night']).agg({'warn_num': 'mean', 'warn_time': 'mean', 'work_percent': 'mean', 'production': 'sum', 'dev_num': 'sum'}).reset_index()
#     print(df_stat)




# 获取当前日期和时间
start_time = datetime(2024, 11, 1, 7, 59, 59)
current_time = datetime.now()
while True:
    if current_time < start_time:
        #跳出循环
        break
    try:
        update_device_vision_day(start_time)
    except Exception as e:
        print(f"An error occurred: {e}")
    start_time = start_time + pd.DateOffset(hours=12)
    print(start_time)
